package com.bw.dg.common;


import com.bw.dg.pojo.People;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.Row;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;


public class POIUtil {

    public static void export(List<People> userList) throws Exception{
        try {
            //指定数据存放的位置
            OutputStream outputStream = new FileOutputStream(new File("D:/people.xls"));
            //1.创建一个工作簿
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建一个sheet
            //2.创建一个工作表sheet
            HSSFSheet sheet = workbook.createSheet("test");
//        sheet.setColumnWidth((short)3, 20* 256);    //---》设置单元格宽度，因为一个单元格宽度定了那么下面多有的单元格高度都确定了所以这个方法是sheet的
//        sheet.setColumnWidth((short)4, 20* 256);    //--->第一个参数是指哪个单元格，第二个参数是单元格的宽度
//        sheet.setDefaultRowHeight((short)300);    // ---->有得时候你想设置统一单元格的高度，就用这个方法
            HSSFDataFormat format= workbook.createDataFormat();   //--->单元格内容格式
            HSSFRow row1 = sheet.createRow(0);   //--->创建一行
            // 四个参数分别是：起始行，起始列，结束行，结束列 (单个单元格)
            sheet.addMergedRegion(new Region(0, (short) 0, 0, (short)18));//可以有合并的作用
            HSSFCell cell1 = row1.createCell((short)0);   //--->创建一个单元格
            //将样式添加生效

            

            for(int i = 0;i<userList.size();i++){
                //行
                HSSFRow row = sheet.createRow(i+1);
//                对列赋值
                row.createCell(0).setCellValue(userList.get(i).getId());
                row.createCell(1).setCellValue(userList.get(i).getName());
                row.createCell(2).setCellValue(userList.get(i).getSex());
                row.createCell(3).setCellValue(userList.get(i).getPhone());
                row.createCell(4).setCellValue(userList.get(i).getWithpeople());
                row.createCell(5).setCellValue(userList.get(i).getDid());



            }
            
            workbook.write(outputStream);
            outputStream.close();
        }catch (Exception e){

        }finally {

        }
    }


    public static List<People> importExcel() throws Exception{
        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(new File("D:/people.xls")));
        HSSFSheet sheet = null;

        for(int i = 0;i < workbook.getNumberOfSheets();i++){
            //获取每个sheet
            sheet = workbook.getSheetAt(i);
            List<People> list = new ArrayList<People>();
            //getPhysicalNumberOfRows获取有记录的行数
            for(int j = 0;j < sheet.getPhysicalNumberOfRows();j++){
                Row row = sheet.getRow(j);
                if(null!=row){
                    //getLastCellNum获取最后一列
                    People user = new People();
                    for(int k = 0;k < row.getLastCellNum();k++){
                        if(null!=row.getCell(k)){
                            if(k==0){
                                Cell cell = row.getCell(0);
                                //cell->double
//                                String stringCellValue = cell.getStringCellValue();
//                                //double->int
//                                long id = new Double(d).intValue();

                                //cell->string  String
                                user.setId(cell.getStringCellValue().toString());
                            }
                            if(k==1){
                                Cell cell = row.getCell(1);
                                //cell->string  String
                                user.setName(cell.getStringCellValue().toString());
                            }
                            if(k==2){
                                DataFormatter formatter = new DataFormatter();
                                Cell cell = row.getCell(2);//int
                                user.setSex((formatter.formatCellValue(cell).toString()));
                            }
                            if(k==3){
                                Cell cell = row.getCell(3);//String
                                user.setPhone(cell.getStringCellValue().toString());
                            }
                            if(k==4){
                                DataFormatter formatter = new DataFormatter();
                                Cell cell = row.getCell(4);//bigint
                                user.setWithpeople((formatter.formatCellValue(cell).toString()));
                            }
                            if(k==5){
                                DataFormatter formatter = new DataFormatter();
                                Cell cell = row.getCell(5);//int
                                user.setId((formatter.formatCellValue(cell)));
                            }

                        }

                    }
                    list.add(user);


                }

            }
            System.out.println("读取sheet表："+ workbook.getSheetName(i) + "完成");
            return list;
        }

        return null;
    }


}